﻿using Dapper;
using System.Data.SqlClient;
using yunbo.Model.BI;

namespace yunbo.Repositorys.BIRepositorys
{
	public class DWRepository
	{
		public string data = "Data Source=121.37.93.71;Initial Catalog=FanXingMES;User Id=sa;Password=geili@2025;TrustServerCertificate=true;";


		/// <summary>
		/// 获取计量单位
		/// </summary>
		/// <param name="state"></param>
		/// <param name="str"></param>
		/// <returns></returns>
		public List<DW> GetDW(int state, string? str)
		{
			List<DW> dw = new List<DW>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select dw_id, dw_code, dw_name, dw_type, dw_jx, dw_hsdw, dw_bl, dw_state
from [dbo].[bd_dw] where [dw_state]=@state and [dw_isdelete] =1 ";
				if (str != null)
				{
					sql += @" and ([dw_code] like @str or [dw_name] like @str or [dw_jx] like @str or [dw_hsdw] like @str) ";
				}

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					command.Parameters.AddWithValue("@state", state);
					if (str != null)
					{
						command.Parameters.AddWithValue("@str", $"%{str}%");
					}

					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							dw.Add(new DW
							{
								dw_id = Convert.ToInt32(reader["dw_id"]),
								dw_code = reader["dw_code"].ToString(),
								dw_name = reader["dw_name"].ToString(),
								dw_type = reader["dw_type"].ToString(),
								dw_jx = reader["dw_jx"].ToString(),
								dw_hsdw = reader["dw_hsdw"].ToString(),
								dw_bl = reader["dw_bl"].ToString(),
								dw_state = Convert.ToInt32(reader["dw_state"])
							});
						}
					}
				}
			}
			return dw;
		}


		/// <summary>
		/// 绑定单位下拉框
		/// </summary>
		/// <returns></returns>
		public List<DW> GetOneDW()
		{

			List<DW> dw = new List<DW>();

			using (SqlConnection connection = new SqlConnection(data))
			{
				string sql = @"select [dw_id],dw_code,[dw_name] from [dbo].[bd_dw] where dw_isdelete=1 and dw_state=1";

				connection.Open();

				using (SqlCommand command = new SqlCommand(sql, connection))
				{
					using (SqlDataReader reader = command.ExecuteReader())
					{
						while (reader.Read())
						{
							dw.Add(new DW
							{
								dw_id = Convert.ToInt32(reader["dw_id"]),
								dw_code = reader["dw_code"].ToString(),
								dw_name = reader["dw_name"].ToString()
							});
						}
					}
				}
			}
			return dw;
		}


		/// <summary>
		/// 改变状态
		/// </summary>
		/// <param name="id"></param>
		/// <param name="state"></param>
		/// <returns></returns>
		public bool StateChange(int id, int state)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"update [dbo].[bd_dw] set [dw_state]=@State where [dw_id]=@Id";
					return connection.Execute(sql, new { Id = id, State = state }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"状态更改失败: {ex.Message}");
				return false;
			}
		}


		/// <summary>
		/// 新增
		/// </summary>
		/// <param name="dw"></param>
		/// <returns></returns>
		public bool AddDW(DW dw)
		{
			string sql = @"insert into [dbo].[bd_dw]
(dw_code, dw_name, dw_type, dw_jx, dw_hsdw, dw_bl, dw_create_by)
values
(@code,@name,@type,@jx,@hsdw,@bl,1)";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@code", dw.dw_code);
						command.Parameters.AddWithValue("@name", dw.dw_name);
						command.Parameters.AddWithValue("@type", dw.dw_type);
						command.Parameters.AddWithValue("@jx", dw.dw_jx);
						command.Parameters.AddWithValue("@hsdw", dw.dw_hsdw);
						command.Parameters.AddWithValue("@bl", dw.dw_bl);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}


		/// <summary>
		/// 修改
		/// </summary>
		/// <param name="dw"></param>
		/// <returns></returns>
		public bool UpdateDW(DW dw)
		{
			string sql = @"update [dbo].[bd_dw] set 
dw_name=@name, dw_type=@type, dw_jx=@jx, dw_hsdw=@hsdw, dw_bl=@bl,[dw_update_by]=1,[dw_update_time]=getdate()
where [dw_id]=@id";

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (SqlCommand command = new SqlCommand(sql, connection))
					{
						command.Parameters.AddWithValue("@name", dw.dw_name);
						command.Parameters.AddWithValue("@type", dw.dw_type);
						command.Parameters.AddWithValue("@jx", dw.dw_jx);
						command.Parameters.AddWithValue("@hsdw", dw.dw_hsdw);
						command.Parameters.AddWithValue("@bl", dw.dw_bl);
						command.Parameters.AddWithValue("@id", dw.dw_id);

						int rowsAffected = command.ExecuteNonQuery();
						return rowsAffected > 0; // 如果插入成功，返回 true
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志或处理异常
				Console.WriteLine("An error occurred: " + ex.Message);
				return false; // 如果发生异常，返回 false
			}
		}


		/// <summary>
		/// 删除
		/// </summary>
		/// <param name="id"></param>
		/// <returns></returns>
		public bool DelDW(int id)
		{
			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					string sql = @"UPDATE [dbo].[bd_dw] 
                       SET dw_isdelete = 0 
                       WHERE dw_id = @Id";

					return connection.Execute(sql, new { Id = id }) > 0;
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"删除失败: {ex.Message}");
				return false;
			}
		}


		/// <summary>
		/// 批量删除
		/// </summary>
		/// <param name="ids"></param>
		/// <returns></returns>
		public bool DelDWs(int[] ids)
		{
			// 参数验证
			if (ids == null || ids.Length == 0)
				return false;

			try
			{
				using (SqlConnection connection = new SqlConnection(data))
				{
					connection.Open();
					using (var transaction = connection.BeginTransaction()) // 添加事务
					{
						try
						{
							string sql = @"UPDATE [dbo].[bd_dw] 
		                                 SET dw_isdelete = 0 
		                                 WHERE dw_id IN @Ids";

							int affectedRows = connection.Execute(sql, new { Ids = ids }, transaction);
							transaction.Commit(); // 提交事务

							// 确保所有ID都成功更新
							return affectedRows == ids.Length;
						}
						catch
						{
							transaction.Rollback(); // 回滚事务
							throw;
						}
					}
				}
			}
			catch (Exception ex)
			{
				// 记录日志等异常处理
				Console.WriteLine($"批量删除失败: {ex.Message}");
				return false;
			}
		}
	}
}
